//
//  HGdlDataBase.m
//  个人项目(豆瓣元旦作业)
//
//  Created by dllo on 16/1/9.
//  Copyright © 2016年 guoguo. All rights reserved.
//

#import "HGdlDataBase.h"

@implementation HGdlDataBase
//创建单例对象
+(HGdlDataBase *)sharedHGdlDataBase{
    
    static HGdlDataBase *hg = nil;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        hg = [[HGdlDataBase alloc] init];
    });
    
    return hg;
}


//打开数据库
- (void)openDB{
    
    //获取沙盒路径
    NSString *document =[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] ;
    //拼接数据库路径
    
    NSString *path = [document stringByAppendingPathComponent:@"DengLu.sqlite"];
    
    
    int result = sqlite3_open(path.UTF8String, &dbOpint);
    
    if (result == SQLITE_OK) {
        NSLog(@"登陆数据库打开成功");
        NSLog(@"%@", path);
    }else{
        NSLog(@"登陆数据库打开失败");
    }
}

//创建表单

- (void)createTable{
    
//    sql语句
    
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists DLtable(number integer primary key autoincrement, textFieldM text, textFieldS text, textFieldY text, textFieldC text, a integer)"];
    
    
    //执行sql语句
    
    int result = sqlite3_exec(dbOpint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"登陆数据库建表成功");
    }else{
        NSLog(@"登陆数据库建表失败");
    }
    
    
}


//插曲数据

- (void)insertData:(DengLuDataBaseModel *)model{
    
    //sql语句
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into DLtable(textFieldM , textFieldS,textFieldY, textFieldC, a )  values('%@', '%@', '%@', '%@', %ld)", model.textFieldM, model.textFieldS, model.textFieldY, model.textFieldC, model.a];
    
    //执行sql语句
    
    int result = sqlite3_exec(dbOpint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"登陆数据库数据插入成功");
    }else{
        NSLog(@"登陆数据库数据插入失败");
    }
    
    
}


- (void)deleteData:(DengLuDataBaseModel *)model{
    
    //sql语句

    NSString *sqlStr = [NSString stringWithFormat:@"delete from DLtable where a = %ld", model.a];
    int result = sqlite3_exec(dbOpint, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"登陆数据库删除数据成功");
    }else{
        NSLog(@"登陆数据库删除数据失败");
    }
    
}

- (void)updateData:(DengLuDataBaseModel *)model{
    
    //sql语句
    
    NSString *sqlStr = [NSString stringWithFormat:@"update DLtable set textFieldM = '%@', textFieldS = '%@' where a = %ld", model.textFieldM, model.textFieldS, model.a];
    int result = sqlite3_exec(dbOpint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"登陆数据库更新数据成功");
    }else{
        NSLog(@"登陆数据库更新数据失败");
    }
    
}

- (NSMutableArray *)selectData{
    
    //1.从数据库中读取数据
    //2.给model赋值
    //3.返回数组
    
    //sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"select * from DLtable"];
    //创建跟随指针
    sqlite3_stmt *stmt = nil;
    
    //开始查询
    
    
    int result = sqlite3_prepare_v2(dbOpint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    NSMutableArray *arr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            DengLuDataBaseModel *model = [DengLuDataBaseModel dengLuDataBaseModel];
            
            const unsigned char *textFieldM = sqlite3_column_text(stmt, 1);
            const unsigned char *textFieldS = sqlite3_column_text(stmt, 2);
            
            model.textFieldM = [NSString stringWithUTF8String:(const char *)textFieldM];
            
            model.textFieldS = [NSString stringWithUTF8String:(const char *)textFieldS];
            
            
            [arr addObject:model];
            
            
        }
        
        
    }else{
        NSLog(@"登陆数据库查询失败");
    }
    
    return arr;
    
}





@end











